const { sequelize } = require('../config/database');
const { Op } = require('sequelize');
const User = require('../models/User');
const Product = require('../models/Product');
const Order = require('../models/Order');
const OrderItem = require('../models/OrderItem');

// 获取仪表板统计数据
exports.getDashboardStats = async (req, res, next) => {
    try {
        const { period = '30d' } = req.query;
        
        // 计算日期范围
        const endDate = new Date();
        let startDate = new Date();
        
        switch (period) {
            case '7d':
                startDate.setDate(endDate.getDate() - 7);
                break;
            case '30d':
                startDate.setDate(endDate.getDate() - 30);
                break;
            case '90d':
                startDate.setDate(endDate.getDate() - 90);
                break;
            case '1y':
                startDate.setFullYear(endDate.getFullYear() - 1);
                break;
            default:
                startDate.setDate(endDate.getDate() - 30);
        }

        // 并行获取各种统计数据
        const [
            totalUsers,
            totalProducts,
            totalOrders,
            totalRevenue,
            newUsers,
            newOrders,
            recentOrders,
            topProducts,
            ordersByStatus,
            salesTrend
        ] = await Promise.all([
            // 总用户数
            User.count(),
            
            // 总商品数
            Product.count({ where: { isActive: true } }),
            
            // 总订单数
            Order.count(),
            
            // 总收入
            Order.sum('totalAmount', {
                where: { status: { [Op.in]: ['paid', 'processing', 'shipped', 'delivered'] } }
            }),
            
            // 新用户数（时间范围内）
            User.count({
                where: {
                    createdAt: { [Op.between]: [startDate, endDate] }
                }
            }),
            
            // 新订单数（时间范围内）
            Order.count({
                where: {
                    createdAt: { [Op.between]: [startDate, endDate] }
                }
            }),
            
            // 最近订单
            Order.findAll({
                limit: 10,
                order: [['createdAt', 'DESC']],
                include: [
                    {
                        model: User,
                        as: 'user',
                        attributes: ['id', 'username', 'firstName', 'lastName']
                    }
                ]
            }),
            
            // 热销商品
            OrderItem.findAll({
                attributes: [
                    'productId',
                    'productName',
                    [sequelize.fn('SUM', sequelize.col('quantity')), 'totalSold'],
                    [sequelize.fn('SUM', sequelize.col('totalPrice')), 'totalRevenue']
                ],
                include: [
                    {
                        model: Order,
                        as: 'order',
                        where: {
                            status: { [Op.in]: ['paid', 'processing', 'shipped', 'delivered'] },
                            createdAt: { [Op.between]: [startDate, endDate] }
                        },
                        attributes: []
                    }
                ],
                group: ['productId', 'productName'],
                order: [[sequelize.fn('SUM', sequelize.col('quantity')), 'DESC']],
                limit: 10
            }),
            
            // 订单状态分布
            Order.findAll({
                attributes: [
                    'status',
                    [sequelize.fn('COUNT', sequelize.col('id')), 'count']
                ],
                group: ['status']
            }),
            
            // 销售趋势（按天）
            Order.findAll({
                attributes: [
                    [sequelize.fn('DATE', sequelize.col('createdAt')), 'date'],
                    [sequelize.fn('COUNT', sequelize.col('id')), 'orderCount'],
                    [sequelize.fn('SUM', sequelize.col('totalAmount')), 'revenue']
                ],
                where: {
                    createdAt: { [Op.between]: [startDate, endDate] },
                    status: { [Op.in]: ['paid', 'processing', 'shipped', 'delivered'] }
                },
                group: [sequelize.fn('DATE', sequelize.col('createdAt'))],
                order: [[sequelize.fn('DATE', sequelize.col('createdAt')), 'ASC']]
            })
        ]);

        res.json({
            success: true,
            data: {
                overview: {
                    totalUsers,
                    totalProducts,
                    totalOrders,
                    totalRevenue: parseFloat(totalRevenue || 0).toFixed(2),
                    newUsers,
                    newOrders
                },
                recentOrders: recentOrders.slice(0, 5),
                topProducts: topProducts.map(item => ({
                    productId: item.productId,
                    productName: item.productName,
                    totalSold: parseInt(item.getDataValue('totalSold')),
                    totalRevenue: parseFloat(item.getDataValue('totalRevenue')).toFixed(2)
                })),
                ordersByStatus: ordersByStatus.reduce((acc, item) => {
                    acc[item.status] = parseInt(item.getDataValue('count'));
                    return acc;
                }, {}),
                salesTrend: salesTrend.map(item => ({
                    date: item.getDataValue('date'),
                    orderCount: parseInt(item.getDataValue('orderCount')),
                    revenue: parseFloat(item.getDataValue('revenue')).toFixed(2)
                }))
            }
        });
    } catch (error) {
        next(error);
    }
};

// 获取销售报告
exports.getSalesReport = async (req, res, next) => {
    try {
        const {
            startDate,
            endDate,
            groupBy = 'day' // day, week, month
        } = req.query;

        if (!startDate || !endDate) {
            return res.status(400).json({
                success: false,
                message: '请提供开始和结束日期'
            });
        }

        let dateFormat;
        switch (groupBy) {
            case 'week':
                dateFormat = '%Y-%u'; // 年-周
                break;
            case 'month':
                dateFormat = '%Y-%m'; // 年-月
                break;
            default:
                dateFormat = '%Y-%m-%d'; // 年-月-日
        }

        const salesData = await Order.findAll({
            attributes: [
                [sequelize.fn('DATE_FORMAT', sequelize.col('createdAt'), dateFormat), 'period'],
                [sequelize.fn('COUNT', sequelize.col('id')), 'orderCount'],
                [sequelize.fn('SUM', sequelize.col('totalAmount')), 'revenue'],
                [sequelize.fn('AVG', sequelize.col('totalAmount')), 'avgOrderValue']
            ],
            where: {
                createdAt: { [Op.between]: [new Date(startDate), new Date(endDate)] },
                status: { [Op.in]: ['paid', 'processing', 'shipped', 'delivered'] }
            },
            group: [sequelize.fn('DATE_FORMAT', sequelize.col('createdAt'), dateFormat)],
            order: [[sequelize.fn('DATE_FORMAT', sequelize.col('createdAt'), dateFormat), 'ASC']]
        });

        res.json({
            success: true,
            data: {
                salesData: salesData.map(item => ({
                    period: item.getDataValue('period'),
                    orderCount: parseInt(item.getDataValue('orderCount')),
                    revenue: parseFloat(item.getDataValue('revenue')).toFixed(2),
                    avgOrderValue: parseFloat(item.getDataValue('avgOrderValue')).toFixed(2)
                }))
            }
        });
    } catch (error) {
        next(error);
    }
};

// 获取产品性能报告
exports.getProductReport = async (req, res, next) => {
    try {
        const {
            page = 1,
            limit = 20,
            sortBy = 'totalSold',
            sortOrder = 'DESC'
        } = req.query;

        const offset = (page - 1) * limit;

        const products = await Product.findAndCountAll({
            attributes: [
                'id', 'name', 'sku', 'price', 'stock', 'salesCount',
                [
                    sequelize.literal(`(
                        SELECT COALESCE(SUM(oi.quantity), 0)
                        FROM order_items oi
                        INNER JOIN orders o ON oi.orderId = o.id
                        WHERE oi.productId = Product.id 
                        AND o.status IN ('paid', 'processing', 'shipped', 'delivered')
                    )`),
                    'totalSold'
                ],
                [
                    sequelize.literal(`(
                        SELECT COALESCE(SUM(oi.totalPrice), 0)
                        FROM order_items oi
                        INNER JOIN orders o ON oi.orderId = o.id
                        WHERE oi.productId = Product.id 
                        AND o.status IN ('paid', 'processing', 'shipped', 'delivered')
                    )`),
                    'totalRevenue'
                ]
            ],
            where: { isActive: true },
            limit: parseInt(limit),
            offset: parseInt(offset),
            order: [[sequelize.literal(sortBy), sortOrder]]
        });

        res.json({
            success: true,
            data: {
                products: products.rows.map(product => ({
                    ...product.toJSON(),
                    totalSold: parseInt(product.getDataValue('totalSold')),
                    totalRevenue: parseFloat(product.getDataValue('totalRevenue')).toFixed(2)
                })),
                pagination: {
                    page: parseInt(page),
                    limit: parseInt(limit),
                    total: products.count,
                    pages: Math.ceil(products.count / limit)
                }
            }
        });
    } catch (error) {
        next(error);
    }
};